/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pooproj.repositorio;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import pooproj.basica.LocMidia;
import pooproj.erro.ConexaoException;
import pooproj.erro.ConsultaException;
import pooproj.util.GerenciadorConexao;
import pooproj.util.GerenciadorConexaoImpl;

/**
 *
 * @author Daniella
 */
public class RepositorioLocMidiaImpl implements RepositorioLocMidia{
    
    GerenciadorConexao gc = GerenciadorConexaoImpl.getInstancia();

    @Override
    public void inserir(LocMidia lm) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "INSERT INTO locmidia (codLoc, codMidia, qtdMidiaLoc, valorMidiaLoc) VALUES (?,?,?,?)";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,lm.getLocacao().getCodLoc());
            pstm.setInt(2,lm.getMidia().getCodMidia());
            pstm.setInt(3,lm.getQtdMidiaLoc());            
            pstm.setDouble(4,lm.getValorMidiaLoc());
            pstm.executeUpdate();
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
        gc.desconectar(con);
        }
        
        //Connection con = gc.conectar();
        //String sql = "INSERT INTO locmidia (codLoc, codMidia, qtdMidiaLoc, valorMidiaLoc) VALUES (?,?,?,?)";
        //try{
        //    PreparedStatement pstm = con.prepareStatement(sql);
        //    pstm.setInt(1, lm.getLocacao().getCodLoc());
        //    pstm.setInt(2,lm.getMidia().getCodMidia());
        //    pstm.setInt(3, lm.getQtdMidiaLoc());
        //    pstm.setDouble(4, lm.getValorMidiaLoc());
        //    pstm.executeUpdate();//Usado para INSERT, UPDATE, DELETE
        //} catch (SQLException e){
        //    throw new ConsultaException(e.getMessage());
        //} finally {
        //    gc.desconectar(con);
        //}
        
    }

    @Override
    public void alterar(LocMidia lm) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "UPDATE locmidia SET qtdMidiaLoc=?,valorMidiaLoc=? WHERE codLoc=? AND codMidia=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, lm.getQtdMidiaLoc());
            pstm.setDouble(2, lm.getValorMidiaLoc());
            pstm.setInt(3, lm.getLocacao().getCodLoc());
            pstm.setInt(4,lm.getMidia().getCodMidia());
            pstm.executeUpdate();//Usado para INSERT, UPDATE, DELETE
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        } 
    }

    @Override
    public void excluir(LocMidia lm) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "DELETE FROM locmidia WHERE codLoc=? AND codMidia=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, lm.getLocacao().getCodLoc());
            pstm.setInt(2,lm.getMidia().getCodMidia());
            pstm.executeUpdate();//Usado para INSERT, UPDATE, DELETE
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        } 
    }

    @Override
    public LocMidia consultarCodLoc(int codLoc) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        LocMidia lm=null;
        String sql = "SELECT locmidia.codLoc, COUNT(locmidia.codMidia) as qtdMidiaLoc, locacao.dtLoc, locacao.dtDevol, sum(locmidia.valorMidiaLoc) as somaValor, locacao.codCliente, cliente.nomeCliente FROM locmidia, locacao, cliente WHERE locmidia.codLoc=locacao.codLoc AND locacao.codCliente = cliente.codCliente AND locmidia.codLoc=?";
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, codLoc);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                lm = new LocMidia();
                lm.getLocacao().setCodLoc(codLoc);
                lm.setQtdMidiaLoc(rs.getInt("qtdMidiaLoc"));
                lm.getLocacao().setDtLoc(rs.getString("dtLoc"));
                lm.getLocacao().setDtDevol(rs.getString("dtDevol"));
                lm.setValorMidiaLoc(rs.getDouble("somaValor"));
                lm.getLocacao().getCliente().setCodCliente(rs.getInt("codCliente"));
                lm.getLocacao().getCliente().setNomeCliente(rs.getString("nomeCliente"));
                       
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        }  
        return lm;
    }

    @Override
    public LocMidia consultarCodMidia(int codMidia) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        String sql = "SELECT codMidia, COUNT(codLoc) as qtdLoc, SUM(valorMidiaLoc) as sumValor FROM locmidia WHERE codMidia=?";
        LocMidia lm=null;
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, codMidia);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()){
                lm = new LocMidia();
                lm.getMidia().setCodMidia(rs.getInt("codMidia"));     
                lm.setQtdMidiaLoc(rs.getInt("qtdLoc"));
                lm.setValorMidiaLoc(rs.getDouble("sumValor"));
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        }  
        return lm;
    }
    
    @Override
        public ArrayList<LocMidia> listarCodMidia(int codMidia) throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        ArrayList<LocMidia> lista = new ArrayList();
        String sql = "SELECT codMidia, codLoc, valorMidiaLoc FROM locmidia WHERE codMidia=?";
        LocMidia lm=null;
        try{
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, codMidia);
            ResultSet rs = pstm.executeQuery();
            while(rs.next()){
                lm = new LocMidia();
                lm.getMidia().setCodMidia(rs.getInt("codMidia"));     
                lm.getLocacao().setCodLoc(rs.getInt("codLoc"));
                lm.setValorMidiaLoc(rs.getDouble("valorMidiaLoc"));
                lista.add(lm);
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        }  
        return lista;
    }

    @Override
    public ArrayList<LocMidia> listar() throws ConsultaException, ConexaoException {
        Connection con = gc.conectar();
        ArrayList<LocMidia> lista = new ArrayList();
        LocMidia lm = null;
        String sql = "SELECT locmidia.codLoc, locmidia.codMidia,  midia.codFilme, filme.nomeFilme, midia.tipoMidia, locacao.dtLoc, locacao.dtDevol, locmidia.valorMidiaLoc, categoria.nomeCateg, cliente.codCliente, cliente.nomeCliente FROM locmidia, midia, locacao, cliente, filme, categoria WHERE locmidia.codMidia = midia.codMidia AND locmidia.codLoc=locacao.codLoc and locacao.codCliente = cliente.codCliente AND midia.codFilme = filme.codFilme AND filme.codCateg=categoria.codCateg";
        try{
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while(rs.next()){
                lm = new LocMidia();
                lm.getLocacao().setCodLoc(rs.getInt("codLoc"));
                lm.getMidia().setCodMidia(rs.getInt("codMidia"));  
                lm.getMidia().getFilme().setCodFilme(rs.getInt("codFilme"));
                lm.getMidia().getFilme().setNomeFilme(rs.getString("nomeFilme"));
                lm.getMidia().setTipoMidia(rs.getString("tipoMidia"));
                lm.getLocacao().setDtLoc(rs.getString("dtLoc"));
                lm.getLocacao().setDtDevol(rs.getString("dtDevol"));
                lm.setValorMidiaLoc(rs.getDouble("valorMidiaLoc"));
                lm.getMidia().getFilme().getCategoria().setNomeCateg(rs.getString("nomeCateg"));
                lm.getLocacao().getCliente().setCodCliente(rs.getInt("codCliente"));
                lm.getLocacao().getCliente().setNomeCliente(rs.getString("nomeCliente"));
                lista.add(lm);
            }
        } catch (SQLException e){
            throw new ConsultaException(e.getMessage());
        } finally {
            gc.desconectar(con);
        }  
        return lista;
    }
    
}
